package es.ibm.santillana.mdm.referentialintegrity;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.ibm.pim.context.Context;
import com.ibm.pim.context.PIMContextFactory;
import com.ibm.pim.search.SearchQuery;
import com.ibm.pim.search.SearchResultSet;

import es.ibm.santillana.mdm.common.util.PIMDBConnection;

public class IntegrityTableLibrary {

	private static String tablename = "catalog_item_reference";
	

	
	
	public IntegrityTableLibrary() throws IOException, SQLException {
		
	}
	
	
	
	public static boolean existeRegistro (String sItem, String sCtg, String valor, String sPath, String sClave,PIMDBConnection dbCon) throws IOException, SQLException {
		
		if (dbCon == null) {
			dbCon = new PIMDBConnection();
		}
		
		String sQuery = "SELECT * FROM " + tablename + " WHERE VALOR = '" + valor + "' AND ITEM =  '" + sItem + "' AND CATALOG_REL = '" + sCtg + "' AND PATH = '" + sPath +"' AND CLAVE = '" + sClave + "'";
		ResultSet rs = dbCon.executeQuery(sQuery);
		
		return  rs.next();
		
		
		
	}
	
	public static boolean insertaRegistro (PIMDBConnection dbCon, String sItem, String sCtg, String valor, String sPath, String sClave) throws IOException, SQLException {
		
		if (dbCon == null) {
			dbCon = new PIMDBConnection();
		}
		
		String sUpdate = "INSERT INTO " + tablename + "(ITEM,CATALOG_REL,CLAVE,VALOR,PATH) VALUES ('" + sItem + "','" + sCtg + "','" + sClave +"','" + valor + "','" + sPath + "')";
		System.out.println(sUpdate);
		Integer numRows = dbCon.executeUpdate(sUpdate);
		
		if (numRows!= null){ 
			return  numRows>0;
		}else{
			return false;
		}
		
		
		
	}
	
	public static boolean borraRegistro (String sItem, String sCtg, String valor, String sPath, String sClave,PIMDBConnection dbCon) throws IOException, SQLException {
		
		if (dbCon == null) {
			dbCon = new PIMDBConnection();
		}
		
		String sUpdate = "DELETE " + tablename + " WHERE ITEM =  '" + sItem + "' AND CATALOG_REL = '" + sCtg + "' AND PATH = '" + sPath +"' AND CLAVE = '" + sClave + "'";
		Integer numRows = dbCon.executeUpdate(sUpdate);
		
		return  numRows>0;
		
		
	}
	
	public static int actualizaValores (String sClave, String sValorAntiguo, String sValorNuevo, String sCtg,PIMDBConnection dbCon) throws IOException, SQLException {
		if (dbCon == null) {
			dbCon = new PIMDBConnection();
		}
		
		String sUpdate = "update " + tablename + " set VALOR =  '" + sValorNuevo + "' WHERE CATALOG_REL = '" + sCtg + "' AND CLAVE = '" + sClave +"' AND VALOR = '" + sValorAntiguo + "'";
		System.out.println(sUpdate);
		Integer numRows = dbCon.executeUpdate(sUpdate);
		if (numRows == null){
			numRows = 0;
		}
		dbCon.commit();
		return numRows;
	}
	public static int actualizaValores (PIMDBConnection dbCon,String sItemPk, String sClave, String sValorNuevo, String sCtg, String path) throws IOException, SQLException {
		if (dbCon == null) {
			dbCon = new PIMDBConnection();
		}
		String sUpdate = "update " + tablename + " set VALOR =  '" + sValorNuevo + "', CLAVE ='"+ sClave +"' WHERE CATALOG_REL = '" + sCtg + "' AND ITEM = '" + sItemPk +"' AND PATH = '" + path + "'";
		System.out.println(sUpdate);
		Integer numRows = dbCon.executeUpdate(sUpdate);
		if (numRows == null){
			numRows = 0;
		}
		
		return numRows;
	}
	public static int actualizaValores (PIMDBConnection dbCon,String sItemPk, String sClave, String sClaveAnt, String sValorNuevo, String sCtg, String path) throws IOException, SQLException {
		if (dbCon == null) {
			dbCon = new PIMDBConnection();
		}
		//ss
		String sUpdate = "update " + tablename + " set VALOR =  '" + sValorNuevo + "', CLAVE ='"+ sClave +"' WHERE CATALOG_REL = '" + sCtg + "' AND ITEM = '"
						+ sItemPk +"' AND PATH = '" + path + "' AND CLAVE = "+sClaveAnt ;
		System.out.println(sUpdate);
		Integer numRows = dbCon.executeUpdate(sUpdate);
		if (numRows == null){
			numRows = 0;
		}
		return numRows;
	}
	public static ArrayList <HashMap <String,String>> getItemAndPathByValue (String sClave, String sValor, String sCtgRel,PIMDBConnection dbCon) throws IOException, SQLException {
		if (dbCon == null) {
			System.out.println("Voy a crear: ");
			dbCon = new PIMDBConnection();
		}
		ArrayList <HashMap <String,String>> listResult = new ArrayList<HashMap<String, String>>();
		String sQuery = "SELECT ITEM,PATH FROM " + tablename + " WHERE CATALOG_REL = '" + sCtgRel + "' AND CLAVE = '" + sClave + "' and VALOR = '" + sValor +"'";
		System.out.println(sQuery);
		ResultSet rs = dbCon.executeQuery(sQuery);
		while (rs.next()) {
			String sItem = rs.getString("ITEM");
			String sPath = rs.getString("PATH");
			HashMap<String, String> hm = new HashMap<String, String>();
			hm.put(sItem, sPath);
			listResult.add(hm);
		}
		return  listResult;
	}
	
	
	
	public static boolean existeValor (String sClave, String sValor, String sCtg_Rel,PIMDBConnection dbCon) throws IOException, SQLException {
		if (dbCon == null) {
			dbCon = new PIMDBConnection();
		}
		String sQuery = "SELECT * FROM " + tablename + " WHERE CATALOG_REL = '" + sCtg_Rel + "' AND CLAVE = '" + sClave + "' AND VALOR = '" + sValor + "'";
		ResultSet rs = dbCon.executeQuery(sQuery);
		
		return  rs.next();
	}
	public static boolean existeValor (PIMDBConnection dbCon, String sItmPk, String path, String sCtg_Rel) throws IOException, SQLException {
		if (dbCon == null) {
			dbCon = new PIMDBConnection();
		}
		String sQuery = "SELECT * FROM " + tablename + " WHERE CATALOG_REL = '" + sCtg_Rel + "' AND ITEM = '" + sItmPk + "' AND PATH like '%"+ path +"'";
		ResultSet rs = dbCon.executeQuery(sQuery);
		
		return  rs.next();
	}
	
	public static boolean existeValorEnCatalogoAuxiliar (String sItmPk, String path)  {
		
		String sCatalogRel = path.split("/")[0];
		if (sCatalogRel != null){
			Context ctxPIM = PIMContextFactory.getCurrentContext();
			String sQuery = "SELECT item.pk FROM catalog('" + sCatalogRel + "') WHERE item['"+path+"'].pk ='"+sItmPk+"'"; 
			System.out.println("Existe Valor? "+sQuery );
			SearchQuery searchQ = ctxPIM.createSearchQuery(sQuery);
			SearchResultSet resSet = searchQ.execute();
			if (resSet.next()){
				return true;
			}else{
				return false;
			}
			
			
		}else{
			return false;
		}
	}
	
	
}
